The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.
A database comprises data and metadata that are stored on one or more storage devices, such as a set of hard disks. The data within a database may be logically organized according to a variety of data models, depending on the implementation. For example, relational database systems typically store data in a set of tables, where each table is organized into a set of rows and columns. In most cases, each row represents a distinct object, and each column represents a distinct attribute. However, other data models may also be used to organize the data.
In order access and manipulate data in a database, a database management system (DBMS) is generally configured to receive and process a variety of database commands, often referred to as queries. In many implementations, the DBMS supports queries that conform to a Data Manipulation Language (DML) such as structured query language (SQL). When the DBMS receives a query, the DBMS performs one or more database operations specified by the query and may output a query result. Example database operations include aggregation and grouping operations, which are described below.
Aggregation and Grouping Operations
Aggregation and grouping operations are database operations that provide summary statistics about data in specific columns. In SQL, grouping operations use the GROUP BY syntax to group results of aggregate functions by one or more columns. Table 1 below illustrates example aggregate functions that may be used in database queries.
TABLE 1Example aggregate functionsFUNCTION NAMEDESCRIPTIONAVGReturns the average value of a columnCOUNTReturns the number of rows in the columnFIRSTReturns the first value in the columnLASTReturns the last value in the columnMAXReturns the largest value in the columnMINReturns the smallest value in the columnSUMReturns the sum of all values in the column
Example aggregation and grouping queries are shown below in Table 2.
TABLE 2Example aggregation queriesQuery 1:SELECTsum(AMOUNT)FROMsalesQuery 2:SELECTSALESMAN, sum (AMOUNT)FROMsalesGROUP BYSALESMANQuery 3:SELECTSALESMAN, CUSTOMER, sum (AMOUNT)FROMsalesGROUP BYSALESMAN, CUSTOMER
Each of the above queries, when executed by the DBMS, aggregates data from values within the AMOUNT column of a sales table. For instance, Table 3 below illustrates an example sales table.
TABLE 3Example sales tableSALE_IDSALESMANCUSTOMERAMOUNT1PedroGainsley Corp.4002PedroLexau's Lexan2003AlexLexau's Lexan1504MichaelLexau's Lexan3505AlexGainsley Corp.6006AlexLexau's Lexan6507PedroGainsley Corp.470
Query 1 requests the total dollar amount of sales the company has made. When Query 1 is executed, the DBMS performs aggregation but no grouping. The DBMS unconditionally sums all amounts in the sales table to return a final result. Given the example sales table of Table 3, Table 4 below illustrates the expected output of executing Query 1.
TABLE 4Result table for Query 1sum (AMOUNT)2820
Query 2 requests the total dollar amount of sales grouped by the salesman who made the sale. When Query 2 is executed, the DBMS performs both grouping and aggregation. Specifically, the DBMS generates one aggregated result for each unique salesman in the sales table where the result is the total sales by the particular salesman. Given the example sales table of Table 3, Table 5 below illustrates the expected output of executing Query 2.
TABLE 5Result table for Query 2SALESMANsum (AMOUNT)Pedro1070Alex1400Michael350
Query 3 requests the total dollar amount of sales grouped by the salesman and the customer associated with the sale. When Query 3 is executed, the DBMS performs multi-column grouping and aggregation. In this case there will be one aggregated result for each unique salesman-customer pair, and the aggregated results are the total sales for that particular salesman-customer pair. Given the example sales table of Table 3, Table 6 below illustrates the expected output of executing Query 3.
TABLE 6Result table for Query 3SALESMANCUSTOMERsum (AMOUNT)PedroGainsley Corp.870PedroLexau's Lexan200AlexGainsley Corp.600AlexLexau's Lexan800MichaelLexau's Lexan350